//使用mysql.createPool
const mysql = require('mysql')
const config = {
    host: 'localhost',
    port: '3306',
    user: 'test',
    password: 'test',
    database: 'test'
}

class model{    
    sFields = "SELECT * ";
    sWhere = "";
    sOrder = "";
    sLimit = "";
    constructor(table) {
      this.table = table;
      this.sTable=" FROM "+ this.table +" ";
      this.pool = mysql.createPool(config)
    }
    table(table_name){
        this.table = table_name;		
		this.sTable=" FROM "+ this.table +" ";
		return this;	
	}
	field(name = '*'){		
		this.sFields = "SELECT "+ name;
		return this;	
	}
	where(name){		
		this.sWhere = " WHERE "+ name;
		return this;	
	}
	order(name){	
		if(name.match('/.*?(ASC|DESC)\s*?/i'))this.sOrder = " ORDER BY "+ name;
		else this.sOrder=" ORDER BY "+ name + " ASC";
		return this;	
	}
	limit(val,ext=''){		
		this.sLimit =" LIMIT "+ val + (ext? "," + ext : '');		
		return this;	
	}
    selsql(count=false){
        if(count)return "SELECT COUNT(*) AS total " + this.sTable + this.sWhere;		
		return this.sFields + this.sTable + this.sWhere + this.sOrder + this.sLimit;
    }
    query(sql, values = [])  {//values =false
        return new Promise((resolve, reject) => {
            this.pool.getConnection((err, connection) => {
                if (err) return reject(err); 
                connection.query(sql, values, (err, result) => {
                    //释放连接
                    connection.release();
                    if (err)return reject(err);
                    resolve(result);
                });
            });
        });
    };
    select(params) {    
        let sql = this.selsql();//'SELECT * FROM '+ this.table + ' WHERE id = ?'    
        return this.query(sql,params);
    }
    insert(params) {
        let sql = 'INSERT INTO '+ this.table + ' SET ?'
        return this.query(sql,params);
    }
    update(params) {
        let sql = 'UPDATE '+ this.table + ' SET ?'
        return this.query(sql,params);
    }
    remove(where){
        let swhere = where? " WHERE " + where : this.sWhere;
		let sql = "DELETE FROM "+ this.table + swhere;
        return this.query(sql);
	} 
}

module.exports = model
  